package _3_0dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import _3_0.pojo.Classes;
import _3_0.pojo.Student;
import _3_0.utils.JDBCUtils;

public class StudentDAO {

	
	// 增加
	public void insert(Student c) throws SQLException{
		
		String sql = "insert into student values (null,?,?,?,?,?)";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		stmt.setString(1, c.getName());
		stmt.setInt(2, c.getChinese());
		stmt.setInt(3, c.getMath());
		stmt.setInt(4, c.getEnglish());
		stmt.setInt(5, c.getClassesId());
		
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 删除
	public void deleteById(int id) throws SQLException{
		String sql = "delete from student where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 修改
	public void update(Student c) throws SQLException{
		
		String sql = "update student set name = ?,"
				+ "chinese = ?,math=?,english=?,class_id=? "
				+ "where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, c.getName());
		stmt.setInt(2, c.getChinese());
		stmt.setInt(3, c.getMath());
		stmt.setInt(4, c.getEnglish());
		stmt.setInt(5, c.getClassesId());
		stmt.setInt(6, c.getId());
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 查询
	public Student findById(int id) throws SQLException{
		String sql = "select * from Student where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		Student c = null;
		if(rs.next())
		{
			String name = rs.getString(2);
			int chinese = rs.getInt(3);
			int math = rs.getInt(4);
			int english = rs.getInt(5);
			int classesId = rs.getInt(6);
			c = new Student(id,name,chinese,math,english,classesId);
		}
		
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, rs);
		
		return c;
	}
	
	// 查询所有
	public List<Student> findAll() throws SQLException{

		String sql = "select * from student";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		List<Student> list = new ArrayList<>();
		while(rs.next())
		{
			int id = rs.getInt(1);
			String name = rs.getString(2);
			int chinese = rs.getInt(3);
			int math = rs.getInt(4);
			int english = rs.getInt(5);
			int classesId = rs.getInt(6);
			Student c = new Student(id,name,chinese,math,english,classesId);
			list.add(c);
		}
		
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, rs);
		
		return list;
		
	
	}
}